package API

import (
	"Library_Management_System/model"
	"Library_Management_System/utils"
)

// GetAllBooks 获取所有图书
func GetAllBooks() ([]*model.Book, error) {
	// sql语句
	sql := "SELECT * FROM books"
	rows, err := utils.Db.Query(sql)
	if err != nil {
		return nil, err
	}
	var books []*model.Book
	for rows.Next() {
		book := &model.Book{}
		err = rows.Scan(&book.ID, &book.Title, &book.Author, &book.Price, &book.TotalQuantity, &book.RemainingQuantity)
		if err != nil {
			return nil, err
		}
		books = append(books, book)
	}
	return books, nil
}

// GetBorrowInfo 根据用户ID获取借阅图书信息
func GetBorrowInfo(userID string) ([]*model.BorrowInfo, error) {
	// sql语句
	sql := "SELECT * FROM borrow_records WHERE user_id =?"
	rows, err := utils.Db.Query(sql, userID)
	if err != nil {
		return nil, err
	}
	var borrowInfos []*model.BorrowInfo
	for rows.Next() {
		borrowInfo := &model.BorrowInfo{}
		err = rows.Scan(&borrowInfo.ID, &borrowInfo.UserID, &borrowInfo.BookID, &borrowInfo.BookName, &borrowInfo.BorrowDate, &borrowInfo.ReturnDate, &borrowInfo.IsOverdue)
		if err != nil {
			return nil, err
		}
		borrowInfos = append(borrowInfos, borrowInfo)
	}
	return borrowInfos, nil
}

// GetBooksByCriteria 根据筛选条件获取图书
func GetBooksByCriteria(title, author string, minPrice, maxPrice float64) ([]*model.Book, error) {
	// sql语句
	sql := "select * from books where title like ? and author like ?"
	rows, err := utils.Db.Query(sql, "%"+title+"%", "%"+author+"%")
	if err != nil {
		return nil, err
	}
	// 判断minPrice和maxPrice是否为空
	if minPrice == 0 && maxPrice == 0 {
		// 最低最高价格都无效
	} else if minPrice == 0 && maxPrice != 0 {
		// 最高价格有效
		sql += " and price <= ?"
		rows, err = utils.Db.Query(sql, "%"+title+"%", "%"+author+"%", maxPrice)
		if err != nil {
			return nil, err
		}
	} else if minPrice != 0 && maxPrice == 0 {
		// 最低价格有效
		sql += " and price >= ?"
		rows, err = utils.Db.Query(sql, "%"+title+"%", "%"+author+"%", minPrice)
		if err != nil {
			return nil, err
		}
	} else {
		// 最低最高价格都有效
		sql += " and price between ? and ?"
		rows, err = utils.Db.Query(sql, "%"+title+"%", "%"+author+"%", minPrice, maxPrice)
	}

	var books []*model.Book
	for rows.Next() {
		book := &model.Book{}
		err = rows.Scan(&book.ID, &book.Title, &book.Author, &book.Price, &book.TotalQuantity, &book.RemainingQuantity)
		if err != nil {
			return nil, err
		}
		books = append(books, book)
	}
	return books, nil
}

// AddBook 添加图书
func AddBook(title, author string, price float64, totalQuantity int) error {
	// sql语句
	sql := "insert into books (title, author, price, total_quantity) values(?, ?, ?, ?)"
	_, err := utils.Db.Exec(sql, title, author, price, totalQuantity)
	if err != nil {
		return err
	}
	return nil
}

// ModifyBook 修改图书
func ModifyBook(bookID, title, author string, price float64, totalQuantity int) error {
	// sql语句
	sql := "update books set title =?, author =?, price =?, total_quantity =? where id =?"
	_, err := utils.Db.Exec(sql, title, author, price, totalQuantity, bookID)
	if err != nil {
		return err
	}
	return nil
}

// DeleteBook 删除图书
func DeleteBook(bookID string) error {
	// sql语句
	sql := "delete from books where id =?"
	_, err := utils.Db.Exec(sql, bookID)
	if err != nil {
		return err
	}
	return nil
}
